// ignore: depend_on_referenced_packages
import 'package:flutter_test/flutter_test.dart';
import 'package:syncfusion_flutter_xlsio/xlsio.dart';
import '../../officechart.dart';
import 'chart.dart';

// ignore: public_member_api_docs
void sampleBrowserSamples() {
  group('Sample Browser Samples', () {
    List<Style> createStyles(Workbook workbook) {
      final Style style1 = workbook.styles.add('style1');
      style1.backColor = '#9BC2E6';
      style1.fontSize = 18;
      style1.bold = true;
      style1.numberFormat = r'$#,##0.00';
      style1.hAlign = HAlignType.center;
      style1.vAlign = VAlignType.center;
      style1.borders.top.lineStyle = LineStyle.thin;
      style1.borders.top.color = '#757171';
      style1.borders.right.lineStyle = LineStyle.thin;
      style1.borders.right.color = '#757171';
      style1.borders.left.lineStyle = LineStyle.thin;
      style1.borders.left.color = '#757171';

      final Style style2 = workbook.styles.add('style2');
      style2.backColor = '#F4B084';
      style2.fontSize = 18;
      style2.bold = true;
      style2.numberFormat = r'$#,##0.00';
      style2.hAlign = HAlignType.center;
      style2.vAlign = VAlignType.center;
      style2.borders.top.lineStyle = LineStyle.thin;
      style2.borders.top.color = '#757171';
      style2.borders.right.lineStyle = LineStyle.thin;
      style2.borders.right.color = '#757171';
      style2.borders.left.lineStyle = LineStyle.thin;
      style2.borders.left.color = '#757171';

      final Style style3 = workbook.styles.add('style3');
      style3.backColor = '#FFD966';
      style3.fontSize = 18;
      style3.bold = true;
      style3.numberFormat = '0.00%';
      style3.hAlign = HAlignType.center;
      style3.vAlign = VAlignType.center;
      style3.borders.top.lineStyle = LineStyle.thin;
      style3.borders.top.color = '#757171';
      style3.borders.right.lineStyle = LineStyle.thin;
      style3.borders.right.color = '#757171';
      style3.borders.left.lineStyle = LineStyle.thin;
      style3.borders.left.color = '#757171';

      final Style style4 = workbook.styles.add('style4');
      style4.backColor = '#A9D08E';
      style4.fontSize = 18;
      style4.bold = true;
      style4.numberFormat = '#,###';
      style4.hAlign = HAlignType.center;
      style4.vAlign = VAlignType.center;
      style4.borders.top.lineStyle = LineStyle.thin;
      style4.borders.top.color = '#757171';
      style4.borders.right.lineStyle = LineStyle.thin;
      style4.borders.right.color = '#757171';
      style4.borders.left.lineStyle = LineStyle.thin;
      style4.borders.left.color = '#757171';

      final Style style5 = workbook.styles.add('style5');
      style5.backColor = '#9BC2E6';
      style5.fontColor = '#757171';
      style5.hAlign = HAlignType.center;
      style5.vAlign = VAlignType.center;
      style5.borders.bottom.lineStyle = LineStyle.thin;
      style5.borders.bottom.color = '#757171';
      style5.borders.right.lineStyle = LineStyle.thin;
      style5.borders.right.color = '#757171';
      style5.borders.left.lineStyle = LineStyle.thin;
      style5.borders.left.color = '#757171';

      final Style style6 = workbook.styles.add('style6');
      style6.backColor = '#F4B084';
      style6.fontColor = '#757171';
      style6.hAlign = HAlignType.center;
      style6.vAlign = VAlignType.center;
      style6.borders.bottom.lineStyle = LineStyle.thin;
      style6.borders.bottom.color = '#757171';
      style6.borders.right.lineStyle = LineStyle.thin;
      style6.borders.right.color = '#757171';
      style6.borders.left.lineStyle = LineStyle.thin;
      style6.borders.left.color = '#757171';

      final Style style7 = workbook.styles.add('style7');
      style7.backColor = '#FFD966';
      style7.fontColor = '#757171';
      style7.hAlign = HAlignType.center;
      style7.vAlign = VAlignType.center;
      style7.borders.bottom.lineStyle = LineStyle.thin;
      style7.borders.bottom.color = '#757171';
      style7.borders.right.lineStyle = LineStyle.thin;
      style7.borders.right.color = '#757171';
      style7.borders.left.lineStyle = LineStyle.thin;
      style7.borders.left.color = '#757171';

      final Style style8 = workbook.styles.add('style8');
      style8.backColor = '#A9D08E';
      style8.fontColor = '#757171';
      style8.hAlign = HAlignType.center;
      style8.vAlign = VAlignType.center;
      style8.borders.bottom.lineStyle = LineStyle.thin;
      style8.borders.bottom.color = '#757171';
      style8.borders.right.lineStyle = LineStyle.thin;
      style8.borders.right.color = '#757171';
      style8.borders.left.lineStyle = LineStyle.thin;
      style8.borders.left.color = '#757171';

      return <Style>[
        style1,
        style2,
        style3,
        style4,
        style5,
        style6,
        style7,
        style8
      ];
    }

    test('Expenses Report', () {
      //Create a Excel document.

      //Creating a workbook.
      final Workbook workbook = Workbook(0);
      //Adding a Sheet with name to workbook.
      final Worksheet sheet1 = workbook.worksheets.addWithName('Budget');
      sheet1.showGridlines = false;

      sheet1.enableSheetCalculations();
      sheet1.getRangeByIndex(1, 1).columnWidth = 19.13;
      sheet1.getRangeByIndex(1, 2).columnWidth = 13.65;
      sheet1.getRangeByIndex(1, 3).columnWidth = 12.25;
      sheet1.getRangeByIndex(1, 4).columnWidth = 11.35;
      sheet1.getRangeByIndex(1, 5).columnWidth = 8.09;
      sheet1.getRangeByName('A1:A18').rowHeight = 20.2;

      //Adding cell style.
      final Style style1 = workbook.styles.add('Style1');
      style1.backColor = '#D9E1F2';
      style1.hAlign = HAlignType.left;
      style1.vAlign = VAlignType.center;
      style1.bold = true;

      final Style style2 = workbook.styles.add('Style2');
      style2.backColor = '#8EA9DB';
      style2.vAlign = VAlignType.center;
      style2.numberFormat = r'[Red]($#,###)';
      style2.bold = true;

      sheet1.getRangeByName('A10').cellStyle = style1;
      sheet1.getRangeByName('B10:D10').cellStyle.backColor = '#D9E1F2';
      sheet1.getRangeByName('B10:D10').cellStyle.hAlign = HAlignType.right;
      sheet1.getRangeByName('B10:D10').cellStyle.vAlign = VAlignType.center;
      sheet1.getRangeByName('B10:D10').cellStyle.bold = true;

      sheet1.getRangeByName('A11:A17').cellStyle.vAlign = VAlignType.center;
      sheet1.getRangeByName('A11:D17').cellStyle.borders.bottom.lineStyle =
          LineStyle.thin;
      sheet1.getRangeByName('A11:D17').cellStyle.borders.bottom.color =
          '#BFBFBF';

      sheet1.getRangeByName('D18').cellStyle = style2;
      sheet1.getRangeByName('D18').cellStyle.vAlign = VAlignType.center;
      sheet1.getRangeByName('A18:C18').cellStyle.backColor = '#8EA9DB';
      sheet1.getRangeByName('A18:C18').cellStyle.vAlign = VAlignType.center;
      sheet1.getRangeByName('A18:C18').cellStyle.bold = true;
      sheet1.getRangeByName('A18:C18').numberFormat = r'$#,###';

      sheet1.getRangeByIndex(10, 1).text = 'Category';
      sheet1.getRangeByIndex(10, 2).text = 'Expected cost';
      sheet1.getRangeByIndex(10, 3).text = 'Actual Cost';
      sheet1.getRangeByIndex(10, 4).text = 'Difference';
      sheet1.getRangeByIndex(11, 1).text = 'Venue';
      sheet1.getRangeByIndex(12, 1).text = 'Seating & Decor';
      sheet1.getRangeByIndex(13, 1).text = 'Technical team';
      sheet1.getRangeByIndex(14, 1).text = 'Performers';
      sheet1.getRangeByIndex(15, 1).text = "Performer's transport";
      sheet1.getRangeByIndex(16, 1).text = "Performer's stay";
      sheet1.getRangeByIndex(17, 1).text = 'Marketing';
      sheet1.getRangeByIndex(18, 1).text = 'Total';

      sheet1.getRangeByName('B11:D17').numberFormat = r'$#,###';
      sheet1.getRangeByName('D11').numberFormat = r'[Red]($#,###)';
      sheet1.getRangeByName('D12').numberFormat = r'[Red]($#,###)';
      sheet1.getRangeByName('D14').numberFormat = r'[Red]($#,###)';

      sheet1.getRangeByName('B11').number = 16250;
      sheet1.getRangeByName('B12').number = 1600;
      sheet1.getRangeByName('B13').number = 1000;
      sheet1.getRangeByName('B14').number = 12400;
      sheet1.getRangeByName('B15').number = 3000;
      sheet1.getRangeByName('B16').number = 4500;
      sheet1.getRangeByName('B17').number = 3000;
      sheet1.getRangeByName('B18').formula = '=SUM(B11:B17)';

      sheet1.getRangeByName('C11').number = 17500;
      sheet1.getRangeByName('C12').number = 1828;
      sheet1.getRangeByName('C13').number = 800;
      sheet1.getRangeByName('C14').number = 14000;
      sheet1.getRangeByName('C15').number = 2600;
      sheet1.getRangeByName('C16').number = 4464;
      sheet1.getRangeByName('C17').number = 2700;
      sheet1.getRangeByName('C18').formula = '=SUM(C11:C17)';

      sheet1.getRangeByName('D11').formula = '=IF(C11>B11,C11-B11,B11-C11)';
      sheet1.getRangeByName('D12').formula = '=IF(C12>B12,C12-B12,B12-C12)';
      sheet1.getRangeByName('D13').formula = '=IF(C13>B13,C13-B13,B13-C13)';
      sheet1.getRangeByName('D14').formula = '=IF(C14>B14,C14-B14,B14-C14)';
      sheet1.getRangeByName('D15').formula = '=IF(C15>B15,C15-B15,B15-C15)';
      sheet1.getRangeByName('D16').formula = '=IF(C16>B16,C16-B16,B16-C16)';
      sheet1.getRangeByName('D17').formula = '=IF(C17>B17,C17-B17,B17-C17)';
      sheet1.getRangeByName('D18').formula = '=IF(C18>B18,C18-B18,B18-C18)';

      final ChartCollection charts = ChartCollection(sheet1);
      final Chart chart = charts.add();
      chart.chartType = ExcelChartType.pie;
      chart.dataRange = sheet1.getRangeByName('A11:B17');
      chart.isSeriesInRows = false;
      chart.chartTitle = 'Event Expenses';
      chart.chartTitleArea.bold = true;
      chart.chartTitleArea.size = 16;
      chart.topRow = 1;
      chart.bottomRow = 10;
      chart.leftColumn = 1;
      chart.rightColumn = 5;
      sheet1.charts = charts;

      final List<int> bytes = workbook.saveAsStream();
      workbook.dispose();

      saveAsExcel(bytes, 'ExpensesReport.xlsx');
    });

    test('Yearly Sale', () {
      //Create a Excel document.

      //Creating a workbook.
      final Workbook workbook = Workbook();
      //Accessing via index
      final Worksheet sheet = workbook.worksheets[0];
      sheet.name = 'Sales Report';
      sheet.showGridlines = false;
      final Worksheet sheet2 = workbook.worksheets.addWithName('Data');
      sheet.enableSheetCalculations();

      sheet.getRangeByIndex(1, 1, 1, 7).merge();
      final Range range = sheet.getRangeByName('A1');
      range.rowHeight = 22.5;
      range.text = 'Yearly Sales';
      range.cellStyle.vAlign = VAlignType.center;
      range.cellStyle.hAlign = HAlignType.center;
      range.cellStyle.bold = true;
      range.cellStyle.fontSize = 14;
      range.cellStyle.backColor = '#9BC2E6';

      sheet.getRangeByName('A1').columnWidth = 2.71;
      sheet.getRangeByName('B1').columnWidth = 10.27;
      sheet.getRangeByName('C1').columnWidth = 10.27;
      sheet.getRangeByName('D1').columnWidth = 0.19;
      sheet.getRangeByName('E1').columnWidth = 10.27;
      sheet.getRangeByName('F1').columnWidth = 10.27;
      sheet.getRangeByName('G1').columnWidth = 2.71;

      sheet.getRangeByIndex(1, 1, 1, 7).merge();

      sheet.getRangeByName('A13').rowHeight = 12;
      sheet.getRangeByName('A14').rowHeight = 21;
      sheet.getRangeByName('A15').rowHeight = 15;
      sheet.getRangeByName('A16').rowHeight = 3;
      sheet.getRangeByName('A17').rowHeight = 21;
      sheet.getRangeByName('A18').rowHeight = 15;
      sheet.getRangeByName('A19').rowHeight = 12;

      final Range range5 = sheet.getRangeByName('B14:C14');
      final Range range6 = sheet.getRangeByName('B15:C15');
      final Range range7 = sheet.getRangeByName('B17:C17');
      final Range range8 = sheet.getRangeByName('B18:C18');
      final Range range9 = sheet.getRangeByName('E14:F14');
      final Range range10 = sheet.getRangeByName('E15:F15');
      final Range range11 = sheet.getRangeByName('E17:F17');
      final Range range12 = sheet.getRangeByName('E18:F18');

      range5.text = r'$ 4.51 M';
      range9.formula = '=Data!D14';
      range7.formula = '=Data!C19';
      range11.formula = '=Data!E14';

      range5.merge();
      range6.merge();
      range7.merge();
      range8.merge();
      range9.merge();
      range10.merge();
      range11.merge();
      range12.merge();

      final List<Style> styles = createStyles(workbook);
      range5.cellStyle = styles[0];
      range9.cellStyle = styles[1];
      range7.cellStyle = styles[2];
      range11.cellStyle = styles[3];

      range6.cellStyle = styles[4];
      range6.text = 'Sales Amount';
      range10.cellStyle = styles[5];
      range10.text = 'Average Unit Price';
      range8.cellStyle = styles[6];
      range8.text = 'Gross Profit Margin';
      range12.cellStyle = styles[7];
      range12.text = 'Customer Count';

      sheet2.getRangeByName('B1').columnWidth = 22.27;
      sheet2.getRangeByName('C1').columnWidth = 22.27;
      sheet2.getRangeByName('D1').columnWidth = 9.27;
      sheet2.getRangeByName('E1').columnWidth = 9.27;

      sheet2.getRangeByName('A1').text = 'Months';
      sheet2.getRangeByName('B1').text = 'Internet Sales Amount';
      sheet2.getRangeByName('C1').text = 'Reseller Sales Amount';
      sheet2.getRangeByName('D1').text = 'Unit Price';
      sheet2.getRangeByName('E1').text = 'Customers';

      sheet2.getRangeByName('A2').text = 'Jan';
      sheet2.getRangeByName('A3').text = 'Feb';
      sheet2.getRangeByName('A4').text = 'Mar';
      sheet2.getRangeByName('A5').text = 'Apr';
      sheet2.getRangeByName('A6').text = 'May';
      sheet2.getRangeByName('A7').text = 'June';
      sheet2.getRangeByName('A8').text = 'Jul';
      sheet2.getRangeByName('A9').text = 'Aug';
      sheet2.getRangeByName('A10').text = 'Sep';
      sheet2.getRangeByName('A11').text = 'Oct';
      sheet2.getRangeByName('A12').text = 'Nov';
      sheet2.getRangeByName('A13').text = 'Dec';
      sheet2.getRangeByName('A14').text = 'Total';

      sheet2.getRangeByName('B2').number = 226170;
      sheet2.getRangeByName('B3').number = 212259;
      sheet2.getRangeByName('B4').number = 181079;
      sheet2.getRangeByName('B5').number = 188809;
      sheet2.getRangeByName('B6').number = 198195;
      sheet2.getRangeByName('B7').number = 235524;
      sheet2.getRangeByName('B8').number = 185786;
      sheet2.getRangeByName('B9').number = 196745;
      sheet2.getRangeByName('B10').number = 164897;
      sheet2.getRangeByName('B11').number = 175673;
      sheet2.getRangeByName('B12').number = 212896;
      sheet2.getRangeByName('B13').number = 325634;
      sheet2.getRangeByName('B14').formula = '=SUM(B2:B13)';

      sheet2.getRangeByName('C2').number = 170234;
      sheet2.getRangeByName('C3').number = 189456;
      sheet2.getRangeByName('C4').number = 168795;
      sheet2.getRangeByName('C5').number = 143567;
      sheet2.getRangeByName('C6').number = 163567;
      sheet2.getRangeByName('C7').number = 163546;
      sheet2.getRangeByName('C8').number = 143787;
      sheet2.getRangeByName('C9').number = 149898;
      sheet2.getRangeByName('C10').number = 153784;
      sheet2.getRangeByName('C11').number = 164289;
      sheet2.getRangeByName('C12').number = 172453;
      sheet2.getRangeByName('C13').number = 223430;
      sheet2.getRangeByName('C14').formula = '=SUM(C2:C13)';

      sheet2.getRangeByName('D2').number = 202;
      sheet2.getRangeByName('D3').number = 204;
      sheet2.getRangeByName('D4').number = 191;
      sheet2.getRangeByName('D5').number = 223;
      sheet2.getRangeByName('D6').number = 203;
      sheet2.getRangeByName('D7').number = 185;
      sheet2.getRangeByName('D8').number = 198;
      sheet2.getRangeByName('D9').number = 196;
      sheet2.getRangeByName('D10').number = 220;
      sheet2.getRangeByName('D11').number = 218;
      sheet2.getRangeByName('D12').number = 299;
      sheet2.getRangeByName('D13').number = 185;
      sheet2.getRangeByName('D14').formula = '=AVERAGE(D2:D13)';

      sheet2.getRangeByName('E2').number = 1861;
      sheet2.getRangeByName('E3').number = 1522;
      sheet2.getRangeByName('E4').number = 1410;
      sheet2.getRangeByName('E5').number = 1488;
      sheet2.getRangeByName('E6').number = 1781;
      sheet2.getRangeByName('E7').number = 2155;
      sheet2.getRangeByName('E8').number = 1657;
      sheet2.getRangeByName('E9').number = 1767;
      sheet2.getRangeByName('E10').number = 1448;
      sheet2.getRangeByName('E11').number = 1556;
      sheet2.getRangeByName('E12').number = 1928;
      sheet2.getRangeByName('E13').number = 2956;
      sheet2.getRangeByName('E14').formula = '=SUM(E2:E13)';

      sheet2.getRangeByName('B17').text = '2018 Sales';
      sheet2.getRangeByName('B18').text = '2018 Sales';
      sheet2.getRangeByName('B19').text = 'Gain %';
      sheet2.getRangeByName('C17').number = 3845634;
      sheet2.getRangeByName('C18').formula = '=B14+C14';
      sheet2.getRangeByName('C19').formula = '=(C18-C17)/10000000';

      sheet2.getRangeByName('C19').numberFormat = '0.00%';
      sheet2.getRangeByName('C17:C18').numberFormat = r'_($* #,##0.00';
      sheet2.getRangeByName('B2:D13').numberFormat = r'_($* #,##0.00';

      sheet2.getRangeByName('A1:E1').cellStyle.backColor = '#C6E0B4';
      sheet2.getRangeByName('A1:E1').cellStyle.bold = true;
      sheet2.getRangeByName('A14:E14').cellStyle.backColor = '#C6E0B4';
      sheet2.getRangeByName('A14:E14').cellStyle.bold = true;
      sheet.getRangeByName('G30').text = '.';

      final ChartCollection charts = ChartCollection(sheet);
      final Chart chart1 = charts.add();
      chart1.chartType = ExcelChartType.column;
      chart1.dataRange = sheet2.getRangeByName('A1:B13');
      chart1.isSeriesInRows = false;
      chart1.chartTitleArea.bold = true;
      chart1.chartTitleArea.size = 12;
      chart1.legend!.position = ExcelLegendPosition.bottom;
      chart1.primaryValueAxis.numberFormat = r'$#,###';
      chart1.primaryValueAxis.hasMajorGridLines = false;
      chart1.topRow = 2;
      chart1.bottomRow = 13;
      chart1.leftColumn = 1;
      chart1.rightColumn = 8;

      final Chart chart2 = charts.add();
      chart2.chartType = ExcelChartType.line;
      chart2.dataRange = sheet2.getRangeByName('A1:C13');
      chart2.isSeriesInRows = false;
      chart2.chartTitleArea.bold = true;
      chart2.chartTitleArea.size = 11;
      chart2.chartTitleArea.color = '#595959';
      chart2.chartTitleArea.text = 'Internet Sales vs Reseller Sales';
      chart2.legend!.position = ExcelLegendPosition.bottom;
      chart2.legend!.textArea.size = 9;
      chart2.legend!.textArea.color = '#595959';
      chart2.topRow = 20;
      chart2.bottomRow = 32;
      chart2.leftColumn = 1;
      chart2.rightColumn = 8;
      chart2.primaryValueAxis.numberFormat = r'$#,###';
      chart2.primaryValueAxis.hasMajorGridLines = false;
      chart2.primaryCategoryAxis.titleArea.size = 9;
      chart2.primaryCategoryAxis.titleArea.color = '#595959';
      chart2.primaryValueAxis.titleArea.size = 9;
      chart2.primaryValueAxis.titleArea.color = '#595959';

      sheet.charts = charts;

      final List<int> bytes = workbook.saveAsStream();
      workbook.dispose();

      saveAsExcel(bytes, 'YearlySale.xlsx');
    });
  });
}
